﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Xml;
using System.Text.RegularExpressions;
using Data.Abstract;
using Data.Configuration;
using System.Data.SQLite;
namespace Data
{
    public class DalPersona
    {
        #region Propiedades
        Context _Context = new Context();
        SQLiteConnection _Conn;
        #endregion
        #region Procedimientos
        public DalPersona() { }
        public DalPersona(SQLiteConnection conn)
        {
            this._Conn = conn;
        }
        #endregion
        #region Metodos
       
        public void Insert(SimplePersona simple)
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                if (!CheckTableExist())
                {
                    CreateTable();
                }
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO Persona (Nombre,Edad,Gen,IdVisitante)values(@Nombre,@Edad,@Gen,@IdVisitante)";

                cmd.Parameters.Add("@Id", DbType.Int32);
                cmd.Parameters.Add("@Nombre", DbType.String);
                cmd.Parameters.Add("@Edad", DbType.Int32);
                cmd.Parameters.Add("@Gen", DbType.String);
                cmd.Parameters.Add("@IdVisitante", DbType.Int32);

                cmd.Parameters[0].Value = simple.Id;
                cmd.Parameters[1].Value = simple.Nombre ;
                cmd.Parameters[2].Value = simple.Edad;
                cmd.Parameters[3].Value = simple.Gen;
                cmd.Parameters[4].Value = simple.IdVisitante;
                conn.Open();
                cmd.ExecuteNonQuery();

            }

        }
        public void Update(SimplePersona simple)
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                if (CheckTableExist())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Update Persona set Nombre = @Nombre,Edad = @Edad,Gen = @Gen,IdVisitante = @IdVisitante where Id = @Id";

                    cmd.Parameters.Add("@Id", DbType.Int32);
                    cmd.Parameters.Add("@Nombre", DbType.String);
                    cmd.Parameters.Add("@Edad", DbType.Int32);
                    cmd.Parameters.Add("@Gen", DbType.String);
                    cmd.Parameters.Add("@IdVisitante", DbType.Int32);
                    
                    cmd.Parameters[0].Value = simple.Id;
                    cmd.Parameters[1].Value = simple.Nombre;
                    cmd.Parameters[2].Value = simple.Edad;
                    cmd.Parameters[3].Value = simple.Gen;
                    cmd.Parameters[4].Value = simple.IdVisitante;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void CreateTable()
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "CREATE TABLE Persona (Id INTEGER PRIMARY KEY AUTOINCREMENT,  Fecha datetime NOT NULL, NewsId int NOT NULL, Visitas int NOT NULL)";
                conn.Open();
                cmd.ExecuteNonQuery();
            }


        }
        public DataTable GetByVisitanteId(int IdVisitante)
        {
            DataTable Out = new DataTable();
            if (CheckTableExist())
            {

                using (SQLiteConnection conn = this._Conn)
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Select * from Persona where IdVisitante = @IdVisitante ";

                    cmd.Parameters.Add("@IdVisitante", DbType.Int32);
                    cmd.Parameters[0].Value = IdVisitante;

                    conn.Open();
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                    sda.Fill(Out);

                }
            }
            return Out;
        }
        public DataTable GetAll()
        {
            DataTable Out = new DataTable();

            if (CheckTableExist())
            {
                using (SQLiteConnection conn = this._Conn)
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Select * from Persona";
                    conn.Open();
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                    sda.Fill(Out);
                }
            }
            return Out;
        }
        public bool CheckTableExist()
        {

            DataTable Out = new DataTable();
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = " SELECT * FROM sqlite_master WHERE name = 'Persona' AND type = 'table'";
                conn.Open();
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                sda.Fill(Out);
                bool SchemaOk = (Out.Rows.Count == 0) ? false : true;
                return SchemaOk;
            }
        }

        #endregion
    }
}